﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

namespace Data
{
    public sealed class ExportExcel
    {
        
        public static string PhieuNhapKho(string sheetName, string path, DataTable title, DataTable content, DataTable bottom)
        {
            try
            {
                var xlApp = new Excel.Application{Visible = true, DisplayAlerts = false};
                Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet.Name = sheetName;
                object missValue = System.Reflection.Missing.Value;
                // thuoc tinh cells
                Excel.Range format = xlWorkSheet.get_Range("A7", "H7");
                format.HorizontalAlignment = Excel.Constants.xlCenter;
                format.VerticalAlignment = Excel.Constants.xlCenter;
                format.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                //format.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlBordersIndex.xlInsideVertical);
                format.Font.Bold = true;
                format.Columns.AutoFit();

                //thuoc tinh cot
                xlWorkSheet.Range["A7", "A7"].ColumnWidth = 5;
                xlWorkSheet.Range["B1", "B1"].ColumnWidth = 13;
                xlWorkSheet.Range["C7", "C7"].ColumnWidth = 18;
                xlWorkSheet.Range["A7", "A7"].Columns.HorizontalAlignment = Excel.Constants.xlCenter;
                xlWorkSheet.Range["A7", "A7"].VerticalAlignment = Excel.Constants.xlCenter;

                for (int i = 0; i < title.Columns.Count; i++)
                {
                    for (int j = 0; j < title.Rows.Count; j++)
                    {
                        xlWorkSheet.Cells[j + 1, i + 1] = title.Rows[j][i].ToString();
                    }
                }

                for (int i = 0; i < content.Columns.Count; i++)
                {
                    for (int j = 0; j < content.Rows.Count; j++)
                    {
                        xlWorkSheet.Cells[j + 8, i + 1] = content.Rows[j][i].ToString();
                        xlWorkSheet.Cells[j + 8, i + 1].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                    }
                }
                for (int i = 0; i < bottom.Columns.Count; i++)
                {
                    for (int j = 0; j < bottom.Rows.Count; j++)
                    {
                        xlWorkSheet.Cells[j + content.Rows.Count + 8, i + 1] = bottom.Rows[j][i].ToString();
                        //xlWorkSheet.Cells[j + 8, i + 1].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                    }
                }

                int dongcuoi = content.Rows.Count + 7;
                xlWorkSheet.Range["A" + dongcuoi, "H" + dongcuoi].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                xlWorkSheet.Range["A" + dongcuoi, "H" + dongcuoi].Font.Bold = true;
                xlWorkSheet.Range["A" + dongcuoi, "F" + dongcuoi].Merge();
                xlWorkSheet.Range["A8", "A" + dongcuoi].HorizontalAlignment = Excel.Constants.xlCenter;
                xlWorkSheet.Range["A8", "A" + dongcuoi].VerticalAlignment = Excel.Constants.xlCenter;
                xlWorkSheet.Range["A8", "A" + dongcuoi].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                xlWorkSheet.Range["F8", "F" + dongcuoi].NumberFormat = "#,###";
                xlWorkSheet.Range["G8", "G" + dongcuoi].NumberFormat = "#,###";
                //
                xlWorkSheet.Range["A" + dongcuoi, "A" + dongcuoi].HorizontalAlignment = Excel.Constants.xlCenter;
                xlWorkSheet.Range["A" + dongcuoi, "A" + dongcuoi].VerticalAlignment = Excel.Constants.xlCenter;
                xlWorkSheet.Range["A" + dongcuoi, "A" + dongcuoi].Value2 = "Tổng tiền";
                //
                int chuviet = content.Rows.Count + 8;
                xlWorkSheet.Range["A" + chuviet, "A" + chuviet].Font.Italic = true;
                xlWorkSheet.Range["A" + chuviet, "H" + chuviet].Merge();
                xlWorkSheet.Range["A" + chuviet, "H" + chuviet].WrapText = true;
                xlWorkSheet.Range["A" + chuviet, "H" + chuviet].RowHeight = 35;

                xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false,
                    Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                //xlWorkBook.Close(truebottom missValue, missValue);
                //xlApp.Application.Quit();
                //xlApp.Quit();
                //xlApp.Visible = true;
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                ////Marshal.FinalReleaseComObject(rangeTemp);
                ////Marshal.FinalReleaseComObject(xlRange);
                //Marshal.FinalReleaseComObject(xlWorkSheet);
                //Marshal.FinalReleaseComObject(xlWorkBook);
                //Marshal.FinalReleaseComObject(xlApp);
            }
            catch (Exception ex)
            {
                foreach (var process in Process.GetProcessesByName("EXCEL"))
                {
                    process.Kill();
                }
                return ex.Message;
            }
            return "true";
        }

        public static string LenhSanXuat(string sheetName, string path, DataTable title, DataTable content, DataTable bottom)
        {
            try
            {
                var xlApp = new Excel.Application { Visible = true, DisplayAlerts = false };
                Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet.Name = sheetName;
                object missValue = System.Reflection.Missing.Value;
                // thuoc tinh cells
                Excel.Range format = xlWorkSheet.get_Range("A7", "P7");
                format.HorizontalAlignment = Excel.Constants.xlCenter;
                format.VerticalAlignment = Excel.Constants.xlCenter;
                format.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                //format.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlBordersIndex.xlInsideVertical);
                format.Font.Bold = true;
                format.Columns.AutoFit();

                //thuoc tinh cot
                xlWorkSheet.Range["A7", "A7"].ColumnWidth = 5;
                xlWorkSheet.Range["B1", "B1"].ColumnWidth = 13;
                xlWorkSheet.Range["C7", "C7"].ColumnWidth = 18;
                xlWorkSheet.Range["A7", "A7"].Columns.HorizontalAlignment = Excel.Constants.xlCenter;
                xlWorkSheet.Range["A7", "A7"].VerticalAlignment = Excel.Constants.xlCenter;

                for (int i = 0; i < title.Columns.Count; i++)
                {
                    for (int j = 0; j < title.Rows.Count; j++)
                    {
                        xlWorkSheet.Cells[j + 1, i + 1] = title.Rows[j][i].ToString();
                    }
                }

                for (int i = 0; i < content.Columns.Count; i++)
                {
                    for (int j = 0; j < content.Rows.Count; j++)
                    {
                        xlWorkSheet.Cells[j + 8, i + 1] = content.Rows[j][i].ToString();
                        xlWorkSheet.Cells[j + 8, i + 1].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                    }
                }
                for (int i = 0; i < bottom.Columns.Count; i++)
                {
                    for (int j = 0; j < bottom.Rows.Count; j++)
                    {
                        xlWorkSheet.Cells[j + content.Rows.Count + 8, i + 1] = bottom.Rows[j][i].ToString();
                        //xlWorkSheet.Cells[j + 8, i + 1].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                    }
                }

                int dongcuoi = content.Rows.Count + 7;
                xlWorkSheet.Range["A" + dongcuoi, "P" + dongcuoi].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;

                xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false,
                    Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                //xlWorkBook.Close(truebottom missValue, missValue);
                //xlApp.Application.Quit();
                //xlApp.Quit();
                //xlApp.Visible = true;
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                ////Marshal.FinalReleaseComObject(rangeTemp);
                ////Marshal.FinalReleaseComObject(xlRange);
                //Marshal.FinalReleaseComObject(xlWorkSheet);
                //Marshal.FinalReleaseComObject(xlWorkBook);
                //Marshal.FinalReleaseComObject(xlApp);
            }
            catch (Exception ex)
            {
                foreach (var process in Process.GetProcessesByName("EXCEL"))
                {
                    process.Kill();
                }
                return ex.Message;
            }
            return "true";
        }

        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }

        protected static bool IsFileLocked(FileInfo file)
        {
            FileStream stream = null;

            try
            {
                stream = file.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
            }
            catch (IOException)
            {
                //the file is unavailable because it is:
                //still being written to
                //or being processed by another thread
                //or does not exist (has already been processed)
                return false;
            }
            finally
            {
                if (stream != null)
                    stream.Close();
            }

            //file is not locked
            return true;
        }

        
    }
}
